package com.cms.biz.queryAnalyse.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.DateUtil;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;
import com.pub.util.StringFunction;


/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPPSHTBMStatusQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";

	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		EFRowSet     queryRowset = EFRowSet.getInstance();
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			
			// 获取查询数据
			queryDataset = queryDataObject(stmt, PO);
			queryRowset.setExtProperty(EMPQueryParamEnum.PAGEQUERY, PO.getValue(EMPQueryParamEnum.PAGEQUERY, null));
			queryRowset.setDataSet(EMPQueryParamEnum.QUERYRESULT, queryDataset);
			// 封装返回数据
		   	RO = new JResponseObject(queryRowset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
//			try {
//				stmt.executeUpdate("drop table "+msTableName);
//			} catch (Exception ex) {
//				ex.printStackTrace();
//			}
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 组织数据的过程
	 * @throws Exception
	 * @return TreeTableDataManager
	 */
	private EFDataSet queryDataObject(Statement stmt, JParamObject po) throws Exception{
		EFDataSet   queryDataset = null;
//		//创建临时表
//	    createTempTable(stmt);
//	    
//	    //取出原始数据
//	    getOriginData(stmt, po);
	    
	    //创建临时表
	    queryDataset = convertDataSet(stmt, po);
		return queryDataset;
	}
	
	/**
	 * 创建临时表
	 * @throws Exception
	 */
	private void createTempTable(Statement stmt) throws Exception{
	    msTableName = "PSHT"+StringFunction.getTempStr(6);
	    String        strSql = "";
	    
	    String tempTableBody = " F_ID          varchar(1)           default ''   null,"	         				
	         				 + " F_KJND        varchar(255)         default ''   null,"
	         				 + " F_DJBH        varchar(255)         default ''   null,"
	         				 + " F_GUID        varchar(255)         default ''   null,"
	         				 + " F_HTBH        varchar(255)         default ''   null,"
	         				 + " F_HTMC        varchar(255)         default ''   null,"
	         				 + " F_XMBH        varchar(255)         default ''   null,"
	         				 + " F_XMMC        varchar(255)         default ''   null,"
	         				 + " F_BMBH        varchar(255)         default ''   null,"
	         				 + " F_BMMC        varchar(255)         default ''   null,"
	         				 + " F_ZDRQ        varchar(255)         default ''   null,"
	         				 + " F_QSRQ        date                              null,"
	         				 + " F_JZRQ        date                              null,"
	         				 + " F_WCTS        int                  default 0    null,"	       
	         				 + " F_NOTE        varchar(255)         default ''   null,"
	         				 + " XG_JZRQ       date                              null,"
	         				 + " XG_WCTS       int                  default 0    null,"	         				 
	         				 + " XG_NOTE       varchar(255)         default 0    null,"
	         				 + " CX_JZRQ       date                              null,"
	         				 + " CX_QRRQ       date                              null,"
	         				 + " F_QRRQ        date                              null";
	    strSql = "create table " + msTableName + "(" + tempTableBody + ")";
	    stmt.execute(strSql);
	  }
	  
	/**
	 * 逐级汇总数据
	 * @throws Exception
	 */
	public void getOriginData(Statement stmt, JParamObject po) throws Exception {
		String              strSql = "";
		String              F_QSRQ = po.GetValueByParamName("F_QSRQ", "");
		String              F_JZRQ = po.GetValueByParamName("F_JZRQ", "");
		String              F_HTBH = po.GetValueByParamName("F_HTBH", "");
		String              F_XMBH = po.GetValueByParamName("F_XMBH", "");
		String              F_BMBH = po.GetValueByParamName("F_BMBH", "");

		//插入入库数量
		strSql = " insert into " + msTableName + " (F_ID, F_KJND,F_DJBH,F_GUID, F_QSRQ, F_JZRQ, F_WCTS, " 
		       + " F_NOTE, XG_JZRQ, XG_WCTS, F_QRRQ, XG_NOTE)"
		       + " select '0', F_KJND,F_DJBH,F_GUID, F_BMBH, F_QSRQ, F_JZRQ, F_WCTS, F_NOTE, XG_JZRQ, XG_WCTS, F_QRRQ, XG_NOTE"
		       + " from PSHTMX"		      
		       + " where F_BMBH like '" + F_BMBH + "%' and"
		       + " exists(select 1 from PSHTXX where PSHTXX.F_KJND = PSHTMX.F_KJND and PSHTXX.F_DJBH = PSHTMX.F_DJBH and " 
		       + " PSHTXX.F_DJZT != '4' and PSHTXX.F_HTBH like '" + F_HTBH + "%' and PSHTXX.F_XMBH like '" + F_XMBH + "%' and " 
		       + " F_ZDRQ <= '" + F_JZRQ + "' and F_ZDRQ >= '" + F_QSRQ + "')";
		stmt.execute(strSql);
	}
	
	public EFDataSet convertDataSet(Statement stmt, JParamObject po) throws Exception {
		EFDataSet queryDataset = null;
		EFDataSet   newDataset = EFDataSet.getInstance();
		ResultSet           rs = null;
		String          strSql = ""; 
		EMPPageQuery pageQuery = (EMPPageQuery) po.getValue(EMPQueryParamEnum.PAGEQUERY, "");
		String         strDate = DateUtil.date3String(DateUtil.PATTERN_DATE);

		String              F_QSRQ = po.GetValueByParamName("F_QSRQ", "");
		String              F_JZRQ = po.GetValueByParamName("F_JZRQ", "");
		String              F_HTBH = po.GetValueByParamName("F_HTBH", "");
		String              F_XMBH = po.GetValueByParamName("F_XMBH", "");
		String              F_KHBH = po.GetValueByParamName("F_KHBH", "");
		String              F_CODE = po.GetValueByParamName("F_CODE", "");
		
		strSql = " select PSHTXX.F_CHDATE as F_CHDATE,PSHTXX.F_CODE as F_CODE,F_CODE.F_BMMC as F_CODEMC,PSHTXX.F_CRDATE as F_CRDATE,"
			   + " PSHTXX.F_DJBH as F_DJBH,PSHTXX.F_DJZT as F_DJZT,PSHTXX.F_FSCS as F_FSCS,PSHTXX.F_GUID as F_GUID,PSHTXX.F_HTBH as F_HTBH," 
			   + " F_HTBH.F_HTMC as F_HTBHMC,PSHTXX.F_KHBH as F_KHBH,F_KHBH.F_KHMC as F_KHBHMC,PSHTXX.F_KJND as F_KJND,PSHTXX.F_NOTE as F_NOTE," 
			   + " PSHTXX.F_PSSJ as F_PSSJ,PSHTXX.F_PSSX as F_PSSX,PSHTXX.F_TJR as F_TJR,F_TJR.USER_NAME as F_TJRMC,PSHTXX.F_TJRQ as F_TJRQ," 
			   + " PSHTXX.F_XMBH as F_XMBH,F_XMBH.F_XMMC as F_XMBHMC,PSHTXX.F_ZDR as F_ZDR,F_ZDR.USER_NAME as F_ZDRMC,PSHTXX.F_ZDRQ as F_ZDRQ " 
			   + " from PSHTXX " 
			   + " LEFT JOIN HYBMZD F_CODE ON F_CODE.F_BMBH = PSHTXX.F_CODE " 
			   + " LEFT JOIN HTXXZD F_HTBH ON F_HTBH.F_HTBH = PSHTXX.F_HTBH " 
			   + " LEFT JOIN KHDAZD F_KHBH ON F_KHBH.F_KHBH = PSHTXX.F_KHBH " 
			   + " LEFT JOIN BSUSER F_TJR ON F_TJR.USER_ID = PSHTXX.F_TJR " 
			   + " LEFT JOIN HTXMZD F_XMBH ON F_XMBH.F_XMBH = PSHTXX.F_XMBH " 
			   + " LEFT JOIN BSUSER F_ZDR ON F_ZDR.USER_ID = PSHTXX.F_ZDR " 
			   + " where F_CODE = '" + F_CODE + "' and F_DJZT = '1' and " 
			   + " PSHTXX.F_HTBH like '" + F_HTBH + "%' and PSHTXX.F_XMBH like '" + F_XMBH + "%' and " 
		       + " PSHTXX.F_KHBH like '" + F_KHBH + "%' and (F_ZDRQ <= '" + F_JZRQ + "' and F_ZDRQ >= '" + F_QSRQ + "')"
			   + " order by PSHTXX.F_KJND desc, PSHTXX.F_DJBH desc";
		
		rs = stmt.executeQuery(strSql);
		queryDataset = DataSetUtils.resultSet2DataSet(rs, queryDataset);

		if(pageQuery == null) return queryDataset;
		
		pageQuery.setTotalCount(queryDataset.getRowCount());
		newDataset = EMPLimitConvertUtil.getDataSetLimit(queryDataset, pageQuery.getPageNum(), pageQuery.getNumPerPage());
		return newDataset;
	}
}
